package com.leenmvc.core.dao.sqlServer;

import com.leenmvc.core.base.BaseEntity;
import com.leenmvc.core.dao.communal.BaseDao;
import com.leenmvc.core.dao.communal.CommonDao;
import com.leenmvc.core.dao.communal.Dialect;
import com.leenmvc.core.dao.communal.Wrapper;
import com.leenmvc.core.dao.sqlServer.connect.SqlServerConnectionFactory;
import com.leenmvc.core.dao.sqlServer.mybatis.MybatisQuery;
import com.leenmvc.core.exception.DaoException;
import com.leenmvc.core.utils.StringUtils;
import com.leenmvc.core.utils.collection.ArrayUtils;
import com.leenmvc.core.base.result.PageData;
import org.springframework.stereotype.Component;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 实现SqlServer的数据库操作方法
 */
@Component
public class SqlServerBaseDaoImpl extends CommonDao implements BaseDao, Serializable {

    private SqlServerConnectionFactory sqlServerConnectionFactory = new SqlServerConnectionFactory();

    private volatile static SqlServerBaseDaoImpl baseDao = null;

    private SqlServerBaseDaoImpl() {
    }

    public static SqlServerBaseDaoImpl getInstance() {
        if (baseDao == null) {
            synchronized (SqlServerBaseDaoImpl.class) {
                if(baseDao == null) {
                    baseDao = new SqlServerBaseDaoImpl();
                }
            }
        }
        return baseDao;
    }

    /**
     * 分页获取记录
     *
     * @return
     */
    public <T> List<T> getPageList(Class<?> cls, Integer... pageInfo) {
        int[] pageInf = pageInfo(pageInfo);
        StringBuilder sql = new StringBuilder("select * from ( ");
        sql.append("select *, ROW_NUMBER() OVER(Order by ");
        sql.append(BaseEntity.getPrimaryColumnName(cls));
        sql.append(") AS RowId from ");
        sql.append(BaseEntity.getTableName(cls));
        sql.append(" ) as b ");
        sql.append(" where RowId between ").append(pageInf[0]).append(" and ").append(pageInf[1]);
        return getListBySql(cls, sql);
    }

    /**
     * 根据条件分页获取指定类型的实体列表
     *
     * @param cls
     * @param conditions
     * @param pageInfo
     * @param <T>
     * @return
     */
    public <T> List<T> getPageList(Class<?> cls, Map<String, Object> conditions, Integer... pageInfo) {
        Object[] sqlParams = new Object[0];
        // 设置分页信息
        int[] pageInf = pageInfo(pageInfo);
        // 清空conditions中的分页信息
        conditions.remove("page");
        conditions.remove("pageSize");
        StringBuilder sql = new StringBuilder("select * from ( ");
        sql.append("select *, ROW_NUMBER() OVER(Order by ");
        sql.append(BaseEntity.getPrimaryColumnName(cls));
        sql.append(") AS RowId from ");
        sql.append(BaseEntity.getTableName(cls));
        sql.append(" ) as b ");
        sql.append(" where RowId between ").append(pageInf[0]).append(" and ").append(pageInf[1]);
        addWhereCondition(conditions, sql);

        List<T> list = new ArrayList<T>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = sqlServerConnectionFactory.getConnection(sql.toString(), cls);
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            int num = 1;
            for (String key : conditions.keySet()) {
                preparedStatement.setObject(num, conditions.get(key));
                sqlParams = ArrayUtils.arrayAdd(sqlParams, conditions.get(key));
                num++;
            }
            resultSet = executeQuery(preparedStatement, sql, sqlParams);
            buildEntityListSimple(cls, resultSet, list);
            return list;
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            sqlServerConnectionFactory.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 根据动态拼接的sql语句返回结果
     *
     * @param queryWrapper
     * @param <T>
     * @return
     */
    @Override
    public <T> List<T> getPageList(Wrapper queryWrapper) {
        Object[] sqlParams = new Object[0];
        int[] pageInf = pageInfo(queryWrapper.getLimit()[0], queryWrapper.getLimit()[1]);
        queryWrapper.getFields().get(0);
        StringBuilder sql = new StringBuilder("select * from ( ");
        sql.append("select *, ROW_NUMBER() OVER(Order by ");
        sql.append(BaseEntity.getPrimaryColumnName(queryWrapper.getCls()));
        sql.append(") AS RowId from ");
        sql.append(BaseEntity.getTableName(queryWrapper.getCls()));
        if (!StringUtils.isEmpty(queryWrapper.getWhere())) sql.append(" where "+queryWrapper.getWhere());
        sql.append(" ) as b ");
        sql.append(" where RowId between ").append(pageInf[0]).append(" and ").append(pageInf[1]);

        List<Object> paramList = queryWrapper.getParamList();
        List<T> list = new ArrayList<T>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = sqlServerConnectionFactory.getConnection(sql.toString(), queryWrapper.getCls());
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            int num = 1;
            for (Object obj : paramList) {
                preparedStatement.setObject(num, obj);
                sqlParams = ArrayUtils.arrayAdd(sqlParams, obj);
                num++;
            }
            resultSet = executeQuery(preparedStatement, sql, sqlParams);
            buildEntityListSimple(queryWrapper, resultSet, list);
            return list;
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            sqlServerConnectionFactory.close(resultSet, preparedStatement, connection);
            Dialect.SQL_SERVER.getWrapperFactory().closeWrapper(queryWrapper);
        }
    }

    /**
     * 从mapper中获取一个(参数为map)
     *
     * @param cls
     * @param batisKey
     * @param params
     * @param <T>
     * @return
     */
    @Override
    public <T> List<T> listBySqlKey(Class<?> cls, String batisKey, Map<String, Object> params) {
        return MybatisQuery.listBySqlKey(cls, batisKey, params);
    }

    /**
     * 从mabatis 的 mapper中获取列表(参数为map)
     *
     * @param cls
     * @param batisKey
     * @param params
     * @param <T>
     * @return
     */
    @Override
    public <T> T getBySqlKey(Class<?> cls, String batisKey, Map<String, Object> params) {
        return MybatisQuery.getBySqlKey(cls, batisKey, params);
    }

    /**
     * 从mybatis中获取一个列表（只有一个参数时）
     *
     * @param cls      实体类
     * @param batisKey sqlkey
     * @param param    参数
     * @param <T>
     * @return
     */
    @Override
    public <T> List<T> listBySqlKey(Class<?> cls, String batisKey, Object param) {
        return MybatisQuery.listBySqlKey(cls, batisKey, param);
    }

    /**
     * 从mybatis中获取一个记录（只有一个参数时候）
     *
     * @param cls      实体类
     * @param batisKey batis中key的值
     * @param param    参数
     * @param <T>
     * @return
     */
    @Override
    public <T> T getBySqlKey(Class<?> cls, String batisKey, Object param) {
//        ConnectionFactory.setSqlSession(cls, batisKey);
        return MybatisQuery.getBySqlKey(cls, batisKey, param);
    }

    /**
     * 从mybatis中获取记录列表（参数为键值对）
     *
     * @param cls      实体类
     * @param batisKey sqlkey
     * @param KVs      参数
     * @param <T>
     * @return
     */
    @Override
    public <T> List<T> listBySqlKey(Class<?> cls, String batisKey, Object... KVs) {
//        ConnectionFactory.setSqlSession(cls, batisKey);
        return MybatisQuery.listBySqlKey(cls, batisKey, KVs);
    }

    /**
     * 从mybatis中获取一条记录（参数为键值对）
     *
     * @param cls      实体类
     * @param batisKey
     * @param KVs      参数
     * @param <T>
     * @return
     */
    @Override
    public <T> T getBySqlKey(Class<?> cls, String batisKey, Object... KVs) {
//        ConnectionFactory.setSqlSession(cls, batisKey);
        return MybatisQuery.getBySqlKey(cls, batisKey, KVs);
    }


    /**
     * 获取分页信息
     *
     * @param pageInfo
     * @return
     */
    private int[] pageInfo(Integer... pageInfo) {
        int start = 0, end = 10;
        if (pageInfo.length == 2) {
            if(pageInfo[1] > PageData.MAX_PAGESIZE) {
                pageInfo[1] = PageData.MAX_PAGESIZE;
            }
            start = pageInfo[0] * pageInfo[1] + 1 - pageInfo[1];
            end = pageInfo[0] * pageInfo[1];
        } else if (pageInfo.length == 1) {
            start = pageInfo[0] * PageData.DEFAULT_PAGESIZE + 1 - PageData.DEFAULT_PAGESIZE;
            end = pageInfo[0] * PageData.DEFAULT_PAGESIZE;
        }

        return new int[]{start, end};
    }

}
